﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CompactMapper.Orm.Extensions;
using CompactMapper.Orm.UnitOfWork.Mappings;
using CompactMapper.Orm.UnitOfWork.Mappings.Meta.PropertyInfos.Columns;
using CompactMapper.Orm.UnitOfWork.Mappings.Meta.Table;

namespace CompactMapper.Orm.UnitOfWork.Commands.Implementations
{
    internal class SqlBuilder
    {
        private const String SqlSpace = " ";
        private const String SqlComma = ",";
        private const String SqlSemiColon = ";";
        private const String SqlOpenParens = "(";
        private const String SqlCloseParens = ")";
        private const String SqlEqual = "=";

        private const String SqlKeyName = "Key";
        private const String SqlPrimaryKey = "PRIMARY KEY";
        private const String SqlCreateTable = "CREATE TABLE";
        private const String SqlCreateIndex = "CREATE INDEX";
        private const String SqlCreateTrigger = "CREATE TRIGGER";
        private const String SqlForeignKey = "FOREIGN KEY";
        private const String SqlReferences = " REFERENCES ";
        private const String SqlAfterDeletOn = "AFTER DELETE ON";
        private const String SqlDeleteCascade = "ON DELETE CASCADE";
        private const String SqlValues = "VALUES";
        private const String SqlInsert = "INSERT INTO";
        private const String SqlSelectAll = "SELECT * FROM";
        private const String SqlWhere = "WHERE";
        private const String SqlAnd = "AND";
        private const String SqlOn = "ON";
        private const String SqlDeleteFrom = "DELETE FROM";
        private const String SqlIndexPrefix = "idx";
        private const String SqlDeleteTriggerPrefix = "triggerDelete";


        private readonly StringBuilder _builder = new StringBuilder();

        public SqlBuilder AppendRaw(String text)
        {
            _builder.Append(text);
            return this;
        }

        public SqlBuilder CreateTable(Table table)
        {
            AppendRaw(SqlCreateTable);
            TableName(table);
            CreateColumns(table);
            Semicolon();
            CreateIndexes(table);
            return this;
        }

        private void CreateIndexes(Table table)
        {
            foreach (var index in table.Meta.Indexes)
            {
                AppendRaw(SqlCreateIndex).
                    Space();
                var indexName = index.Key;
                if (String.IsNullOrEmpty(indexName))
                    AppendRaw(SqlIndexPrefix).AppendRaw(table.Name);
                else
                    AppendRaw(indexName);
                Space().
                AppendRaw(SqlOn).
                Space().
                AppendRaw(table.Name).
                Space().
                OpenParens();
                foreach (var column in index.Value)
                {
                    AppendRaw(column.ColumnName).Comma();
                }
                var alsoInclude = index.Value.SelectMany(i => i.IncludeProperties).Distinct();
                if (alsoInclude.HasItems())
                {
                    foreach (var property in alsoInclude)
                    {
                        AppendRaw(property).Comma();
                    }
                }
                RemoveLastChar().CloseParens().Semicolon();
            }
        }

        private void KeyColumn(TableMetaInfo tableMetaInfo)
        {
            AppendRaw(tableMetaInfo.Name.ToSqlColumn()).ColumnType(tableMetaInfo.PrimaryKeyColumn);
        }

        public SqlBuilder CreateJoinTable(Relationship relationship, TablesTree tablesTree)
        {
            AppendRaw(SqlCreateTable);
            AppendRaw(relationship.JoinTableName.ToSqlTableName());
            OpenParens();
            KeyColumn(tablesTree.GetTable(relationship.ParentTableName).Meta);
            Comma();
            KeyColumn(tablesTree.GetTable(relationship.ChildTableName).Meta);
            Comma();
            AppendRaw(SqlForeignKey);
            OpenParens();
            AppendRaw(relationship.ParentTableName);
            CloseParens();
            AppendRaw(SqlReferences);
            AppendRaw(relationship.ParentTableName);
            OpenParens();
            AppendRaw(SqlKeyName);
            CloseParens();
            Space();
            AppendRaw(SqlDeleteCascade);
            Comma();
            AppendRaw(SqlForeignKey);
            OpenParens();
            AppendRaw(relationship.ChildTableName);
            CloseParens();
            AppendRaw(SqlReferences);
            AppendRaw(relationship.ChildTableName);
            OpenParens();
            AppendRaw(SqlKeyName);
            CloseParens();
            Space();
            AppendRaw(SqlDeleteCascade);
            CloseParens();
            Semicolon();
            TriggerDelete(relationship);
            JoinTablesIndexes(relationship);
            return this;
        }

        private SqlBuilder TriggerDelete(Relationship relationship)
        {
            return AppendRaw(SqlCreateTrigger).
                Space().
                AppendRaw(SqlDeleteTriggerPrefix).
                AppendRaw(relationship.JoinTableName).
                Space().
                AppendRaw(SqlAfterDeletOn).
                Space().
                AppendRaw(relationship.JoinTableName).
                AppendRaw(" FOR EACH ROW BEGIN").
                Space().
                AppendRaw(SqlDeleteFrom).
                Space().
                AppendRaw(relationship.ChildTableName).
                Where().
                AppendRaw(relationship.ChildTableName).
                AppendRaw(".Key = old.").
                AppendRaw(relationship.ChildTableName).
                Semicolon().
                AppendRaw(" END").
                Semicolon();
        }


        private SqlBuilder JoinTablesIndexes(Relationship relationship)
        {
            AppendRaw(SqlCreateIndex).
                   Space().
                   AppendRaw(SqlIndexPrefix).AppendRaw(relationship.JoinTableName).
                   Space().
                   AppendRaw(SqlOn).
                   Space().
                   AppendRaw(relationship.JoinTableName).
                   Space().
                   OpenParens().
                   AppendRaw(relationship.ParentTableName).
                   CloseParens().
                   Semicolon();

            AppendRaw(SqlCreateIndex).
                Space().
                AppendRaw(SqlIndexPrefix).AppendRaw(relationship.ChildTableName).AppendRaw(relationship.ParentTableName).
                Space().
                AppendRaw(SqlOn).
                Space().
                AppendRaw(relationship.JoinTableName).
                Space().
                OpenParens().
                AppendRaw(relationship.ChildTableName).
                CloseParens().
                Semicolon();
            return this;
        }


        public SqlBuilder TableName(Table table)
        {
            return AppendRaw(table.Name.ToSqlTableName());
        }

        public SqlBuilder RemoveLastChar()
        {
            return RemoveLastChars(1);
        }

        public SqlBuilder RemoveLastChars(int length)
        {
            _builder.Remove(_builder.Length - length, length);
            return this;
        }

        public SqlBuilder Space()
        {
            return AppendRaw(SqlSpace);
        }

        public SqlBuilder Comma()
        {
            return AppendRaw(SqlComma);
        }

        public SqlBuilder Semicolon()
        {
            return AppendRaw(SqlSemiColon);
        }

        public SqlBuilder OpenParens()
        {
            return AppendRaw(SqlOpenParens);
        }

        public SqlBuilder CloseParens()
        {
            return AppendRaw(SqlCloseParens);
        }

        public SqlBuilder PrimaryKey(IPrimaryKeyColumn primaryKeyColumn)
        {
            return AppendRaw("[Key]").ColumnType(primaryKeyColumn).Space().AppendRaw(SqlPrimaryKey);
        }

        public SqlBuilder PrimaryKeyForJoinTable()
        {
            return AppendRaw("[Key][guid] PRIMARY KEY");
        }

        public SqlBuilder CreateColumn(Column column)
        {
            if (column is IPrimaryKeyColumn)
            {
                return PrimaryKey((IPrimaryKeyColumn)column);
            }
            return ColumnName(column).Space().ColumnType(column);
        }

        public SqlBuilder ColumnName(IColumn column)
        {
            return AppendRaw(column.Name.ToSqlColumn());
        }

        public SqlBuilder ColumnType(IColumn column)
        {
            return AppendRaw(column.SqlType);
        }

        public SqlBuilder ColumnValue(IColumn column)
        {
            return AppendRaw(column.Name.ToSqlValue());
        }

        public SqlBuilder CreateColumns(Table table)
        {
            OpenParens();
            foreach (var column in table.Meta.Columns)
            {
                CreateColumn(column).Comma();
            }
            RemoveLastChar();
            CloseParens();
            return this;
        }

        public SqlBuilder Insert(Table table)
        {
            AppendRaw(SqlInsert);
            TableName(table);
            InsertColumns(table);
            InsertValues(table);
            Semicolon();
            return this;
        }

        private void InsertColumns(Table table)
        {
            OpenParens();
            foreach (var column in table.Meta.Columns)
            {
                ColumnName(column).Comma();
            }

            RemoveLastChar();
            CloseParens();
        }

        private void InsertValues(Table table)
        {
            AppendRaw(SqlValues);
            OpenParens();
            foreach (var column in table.Meta.Columns)
            {
                ColumnValue(column).Comma();
            }
            RemoveLastChar();
            CloseParens();
        }

        public SqlBuilder InsertJoinTable(Relationship relationship)
        {
            AppendRaw(SqlInsert);
            AppendRaw(relationship.JoinTableName.ToSqlTableName());
            AppendRaw(SqlValues);
            OpenParens();
            AppendRaw(relationship.ParentTableName.ToSqlValue()).Comma();
            AppendRaw(relationship.ChildTableName.ToSqlValue());
            CloseParens();
            return this;
        }

        public SqlBuilder SelectAll(Table table)
        {
            AppendRaw(SqlSelectAll);
            Space();
            TableName(table);
            return this;
        }

        public SqlBuilder Where()
        {
            return Space().AppendRaw(SqlWhere).Space();
        }

        private void And()
        {
            Space().AppendRaw(SqlAnd).Space();
        }

        public SqlBuilder Query(Table table, IEnumerable<String> clauses)
        {
            SelectAll(table);
            Where();
            var lastClause = clauses.Last();
            clauses.Except(new[] { lastClause }).Apply(clause => AppendRaw(clause).And());
            AppendRaw(lastClause);
            return this;
        }

        public SqlBuilder Query(String tableName, IEnumerable<String> clauses)
        {
            AppendRaw(SqlSelectAll);
            Space();
            AppendRaw(tableName.ToSqlTableName());
            Where();
            var lastClause = clauses.Last();
            clauses.Except(new[] { lastClause }).Apply(clause => AppendRaw(clause).And());
            AppendRaw(lastClause);
            return this;
        }

        private void Equal()
        {
            AppendRaw(SqlEqual);
        }

        public SqlBuilder DeleteAllFrom(Table table)
        {
            AppendRaw(SqlDeleteFrom);
            TableName(table);
            return this;
        }

        public SqlBuilder DeleteFrom(Table table)
        {
            AppendRaw(SqlDeleteFrom);
            TableName(table);
            Space();
            Where();
            Space();
            AppendRaw(SqlKeyName.ToSqlColumn());
            Equal();
            AppendRaw(SqlKeyName.ToSqlValue());
            return this;
        }

        public override String ToString()
        {
            return _builder.ToString();
        }
    }
}